package com.iswoak.netctoss.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.iswoak.netctoss.dao.RoleInfoDao;
import com.iswoak.netctoss.entity.RoleInfo;
import com.iswoak.netctoss.entity.RoleInfoVo;
import com.iswoak.netctoss.util.DBUtil;

public class RoleInfoDaoImpl implements RoleInfoDao{
	
	@Override
	public List<RoleInfoVo> page(Integer currPage,Integer pageSize) {
		Connection conn=DBUtil.getConnection();
		Statement stmt=null;
		List<RoleInfoVo> list=null;
		
		String sql="SELECT ri.ROLE_ID,ri.`NAME` roleName,GROUP_CONCAT(mi.`NAME` SEPARATOR ',') moduleNames "
				+ "FROM role_info ri "
				+ "LEFT JOIN role_module rm ON ri.ROLE_ID=rm.ROLE_ID "
				+ "LEFT JOIN module_info mi ON rm.MODULE_ID=mi.MODULE_ID "
				+ "WHERE 1=1 ";
		//处理查询条件
/*		if(StringUtils.isBlank(roleInfoVo.getRoleName())){
			sql+=" AND ri.`NAME` LIKE '%"+roleInfoVo.getRoleName()+"%'";
		}*/
		sql+=" GROUP BY ri.ROLE_ID";
/*		if(StringUtils.isBlank(roleInfoVo.getModuleNames())){
			sql+=" HAVING moduleNames LIKE '%"+roleInfoVo.getModuleNames()+"%'";
		}*/
		sql+=" LIMIT "+currPage+","+pageSize;
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			//创建返回结果集
			list=new ArrayList<RoleInfoVo>();
			//处理结果集
			while(rs.next()){
				list.add(new RoleInfoVo(
						rs.getInt("role_id"), 
						rs.getString("roleName"), 
						rs.getString("moduleNames")));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			DBUtil.closeConnection(conn);
		}
		return list;
	}

	@Override
	public int count() {
		Connection conn=DBUtil.getConnection();
		Statement stmt=null;
		int count=0;
		String sql="SELECT count(DISTINCT ri.ROLE_ID) "
				+ "FROM role_info ri "
				+ "LEFT JOIN role_module rm ON ri.ROLE_ID=rm.ROLE_ID "
				+ "LEFT JOIN module_info mi ON rm.MODULE_ID=mi.MODULE_ID "
				+ "where 1=1 ";
		//处理查询条件
//		if(roleInfoVo.getRoleName()!=null&&!roleInfoVo.getRoleName().equals("")){
//			sql+=" AND ri.`NAME` LIKE '%"+roleInfoVo.getRoleName()+"%'";
//		}
//		if(roleInfoVo.getModuleNames()!=null&&!roleInfoVo.getModuleNames().equals("")){
//			sql+=" and mi.`name` LIKE '%"+roleInfoVo.getModuleNames()+"%'";
//		}
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				count=rs.getInt(1);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			DBUtil.closeConnection(conn);
		}
		return count;
	}

	@Override
	public int add(RoleInfo roleInfo) {
		Connection conn=DBUtil.getConnection();
		int key=0;
		PreparedStatement ps=null;
		String sql="insert into role_info(role_id,name) values(null,?)";
		try {
			ps=conn.prepareStatement(sql,new String[]{"role_id"});
			//设置参数
			ps.setString(1, roleInfo.getName());
			ps.executeUpdate();
			//得到自动增长的主键
			ResultSet rs=ps.getGeneratedKeys();
			while(rs.next()){
				key=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeConnectionAndPreparedStatement(conn, ps);
		}
		
		return key;
		
	}

	@Override
	public RoleInfoVo findByRoleId(Integer roleId) {
		Connection conn=DBUtil.getConnection();
		PreparedStatement ps=null;
		RoleInfoVo roleInfoVo=null;
		String sql="SELECT ri.ROLE_ID,ri.`NAME` roleName,GROUP_CONCAT(mi.`NAME` SEPARATOR ',') moduleNames "
				+ "FROM role_info ri "
				+ "LEFT JOIN role_module rm ON ri.ROLE_ID=rm.ROLE_ID "
				+ "LEFT JOIN module_info mi ON rm.MODULE_ID=mi.MODULE_ID "
				+ "GROUP BY ri.ROLE_ID "
				+ "HAVING ri.ROLE_ID=?";
		try {
			ps=conn.prepareStatement(sql);
			//设置参数
			ps.setInt(1, roleId);
			ResultSet rs=ps.executeQuery();
			//处理结果集
			while(rs.next()){
				roleInfoVo=new RoleInfoVo(
						roleId,
						rs.getString("roleName"),
						rs.getString("moduleNames"));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeConnectionAndPreparedStatement(conn, ps);
		}
		return roleInfoVo;
	}

	@Override
	public void update(RoleInfo roleInfo) {
		Connection conn=DBUtil.getConnection();
		PreparedStatement ps=null;
		String sql="update role_info set name=? where role_id=?";
		try {
			ps=conn.prepareStatement(sql);
			//设置参数
			ps.setString(1, roleInfo.getName());
			ps.setInt(2, roleInfo.getRoleId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeConnectionAndPreparedStatement(conn, ps);
		}
	}

	@Override
	public void remove(Integer roleId) {
		Connection conn=DBUtil.getConnection();
		PreparedStatement ps=null;
		String sql="delete from role_info where role_id=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, roleId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeConnectionAndPreparedStatement(conn, ps);
		}
	}

	@Override
	public RoleInfo findByRoleName(String roleName,String roleId) {
		Connection conn=DBUtil.getConnection();
		PreparedStatement ps=null;
		RoleInfo roleInfo=null;
		String sql="select * from role_info where name = ? ";
		if(!StringUtils.isBlank(roleId)) {
			sql += "and role_id != ? ";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, roleName);
			if(!StringUtils.isBlank(roleId)) {
				ps.setInt(2, Integer.valueOf(roleId));
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				roleInfo = new RoleInfo(rs.getInt("role_id"), rs.getString("name"));
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeConnectionAndPreparedStatement(conn, ps);
		}
		return roleInfo;
	}

}
